package com.rumbo.hotel.scriptGenerator;

import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

/**
 * Hello world!
 * 
 */
public class Puppets
{
    private static String dbChainConnection = "";
    private static String user = "";
    private static String pass = "";

    private final static String dbChainConnectionSTG = "jdbc:oracle:thin:@10.150.9.134:1521/rumstgsvc";
    private final static String userSTG = "hoteldev";
    private final static String passSTG = "hoteldev01";

    private final static String dbChainConnectionPRD = "jdbc:oracle:thin:@scanprd.rumbo.com:1521/rumprdsvc";
    private final static String userPRD = "HOTEL_PRD_LIVE_API";
    private final static String passPRD = "HOTEL_PRD_LIVE_API01";
    private static String environment;
    private static String table2Migrate;

    public static void main(String[] args)
    {

        environment = args[0];
        table2Migrate = args[1];

        selectEnvironment(environment);

        Connection conn = establishConnection();

        if ("DEFAULT_METADATA".equals(table2Migrate))
        {
            generateForDEFAULT_METADATA(conn, table2Migrate);
            System.exit(0);
        }
        else if ("HOTEL_USER_METADATA".equals(table2Migrate))
        {
            generateForHOTEL_METADATA(conn, table2Migrate);
            System.exit(0);
        }
        else
        {
            System.exit(1);
        }

    }

    private static void generateForDEFAULT_METADATA(Connection conn,
                                                    String table)
    {

        Map<Map<String, String>, List<String>> map = new HashMap<Map<String, String>, List<String>>();

        Statement stmt;
        try
        {
            stmt = conn.createStatement();

            System.out.println("executing Query.. ");
            String sql = "select "
                    + "d3.vdescriptionkey as metadata_key, d3.vdescription as metadata_description,d3.vvalue as metadata_value,"
                    + " d3.nserviciospaises as country_service_id,'now()' as enabled_date, 'now()' as last_modified_date "
                    + "from dhotmetadata d3";

            ResultSet rs = execQuery(stmt, sql);

            System.out.println("Query done ! ");

            long ini = System.currentTimeMillis();
            int i = 0;
            while (rs.next())
            {
                buildInsertMap(map, rs, "metadata_key", "country_service_id");
                i++;
            }
            long end = System.currentTimeMillis();

            System.out.println("leidos  " + i + "registros (" + (end - ini) / 1000 + " seg)");
            System.out.println("eliminando duplicacados generaremos  " + map.size()
                    + " inserts ..");

            ini = System.currentTimeMillis();
            int nscripts = generarScripts(map, table);
            end = System.currentTimeMillis();

            System.out.println("FIN . Generados " + nscripts + " inserts  (" + (end - ini) / 1000
                    + " seg)");

        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    private static void generateForHOTEL_METADATA(Connection conn,
                                                  String table)
    {
        Map<Map<String, String>, List<String>> map = new HashMap<Map<String, String>, List<String>>();

        Statement stmt;
        try
        {
            stmt = conn.createStatement();

            System.out.println("executing Query.. ");
            String sql = "select METADATA_KEY,METADATA_DESCRIPTION,METADATA_VALUE,USER_ID,'now()' ENABLED_DATE,'now()' LAST_MODIFIED_DATE"
                    + " from hotel_user_metadata";

            ResultSet rs = execQuery(stmt, sql);

            System.out.println("Query done ! ");

            long ini = System.currentTimeMillis();
            int i = 0;
            while (rs.next())
            {
                buildInsertMap(map, rs, "METADATA_KEY", "USER_ID");
                i++;
            }
            long end = System.currentTimeMillis();

            System.out.println("leidos  " + i + " registros (" + (end - ini) / 1000 + " seg)");
            System.out
                    .println("eliminando duplicados generaremos  " + map.size() + " inserts ..");

            ini = System.currentTimeMillis();
            int nscripts = generarScripts(map, table);
            end = System.currentTimeMillis();

            System.out.println("FIN . Generados " + nscripts + " inserts  (" + (end - ini) / 1000
                    + " seg)");

        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    private static void selectEnvironment(String environment)
    {
        if ("STG".equals(environment))
        {
            dbChainConnection = dbChainConnectionSTG;
            user = userSTG;
            pass = passSTG;
        }
        else if ("PRD".equals(environment))
        {
            dbChainConnection = dbChainConnectionPRD;
            user = userPRD;
            pass = passPRD;
        }
        else if ("PHX".equals(environment))
        {
            System.exit(1);
        }
        else
        {
            System.exit(1);
        }
    }

    private static void buildInsertMap(Map<Map<String, String>, List<String>> map,
                                       ResultSet rs,
                                       String nameFieldForIntegrityConstraint1,
                                       String nameFieldForIntegrityConstraint2)
            throws SQLException
    {
        String key = rs.getString(nameFieldForIntegrityConstraint1);
        String sp = rs.getString(nameFieldForIntegrityConstraint2);

        Map<String, String> mapKey = new HashMap<String, String>();// constraint key-sp
                                                                   // unique
        mapKey.put(key, sp);

        List<String> valuesList = new ArrayList<String>();
        valuesList.add(rs.getString(1));
        valuesList.add(rs.getString(2));
        valuesList.add(rs.getString(3));
        valuesList.add(rs.getString(4));
        valuesList.add(rs.getString(5));
        valuesList.add(rs.getString(6));

        map.put(mapKey, valuesList);
    }

    private static ResultSet execQuery(Statement stmt,
                                       String sql) throws SQLException
    {
        ResultSet rs = stmt.executeQuery(sql);
        return rs;
    }

    private static int generarScripts(Map<Map<String, String>, List<String>> map,
                                      String table)
    {
        System.out.println("TODO: generador de inserts");

        Set<Entry<Map<String, String>, List<String>>> set = map.entrySet();

        Iterator<Entry<Map<String, String>, List<String>>> it = set.iterator();
        int cont = 0;
        String allSqlInserts = "";

        StringBuilder sb = new StringBuilder();
        while (it.hasNext())
        {
            Map.Entry<java.util.Map<java.lang.String, java.lang.String>, List<String>> entry = (Map.Entry<java.util.Map<java.lang.String, java.lang.String>, List<String>>) it
                    .next();

            List<String> rs = entry.getValue();

            sb.append(buildInsertSintax(rs, table));

            cont++;

        }

        allSqlInserts = sb.toString();

        allSqlInserts = allSqlInserts.replace("'now()'", "now()");

        escribir(allSqlInserts, table);

        return cont;

    }

    public static void escribir(String s,
                                String table)
    {
        FileWriter fichero = null;
        PrintWriter pw = null;
        try
        {
            fichero = new FileWriter("insert_" + table + "_MYSQL_" + environment + ".sql");
            pw = new PrintWriter(fichero);

            pw.println(s);

        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                // Nuevamente aprovechamos el finally para
                // asegurarnos que se cierra el fichero.
                if (null != fichero)
                    fichero.close();
            }
            catch (Exception e2)
            {
                e2.printStackTrace();
            }
        }
    }

    private static StringBuilder buildInsertSintax(List<String> l,
                                                   String table)
    {
        StringBuilder sb = new StringBuilder();

        if ("HOTEL_USER_METADATA".equals(table))
        {
            try
            {

                // Insert into HOTEL_USER_METADATA
                // (METADATA_KEY,METADATA_DESCRIPTION,METADATA_VALUE,USER_ID,ENABLED_DATE,LAST_MODIFIED_DATE)
                // values ('
                sb.append("Insert into "
                        + table
                        + " (METADATA_KEY,METADATA_DESCRIPTION,METADATA_VALUE,USER_ID,ENABLED_DATE,LAST_MODIFIED_DATE) values ('");
                sb.append(l.get(0));
                sb.append("','");
                sb.append(l.get(1));
                sb.append("','");
                sb.append(l.get(2));
                sb.append("','");
                sb.append(l.get(3));
                sb.append("','");
                sb.append(l.get(4));
                sb.append("','");
                sb.append(l.get(5));
                sb.append("');");

                sb.append("\n");

            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        else if ("DEFAULT_METADATA".equals(table))
        {
            try
            {
                sb.append("Insert into "
                        + table
                        + " (METADATA_KEY,METADATA_DESCRIPTION,METADATA_VALUE,COUNTRY_SERVICE_ID,ENABLED_DATE,LAST_MODIFIED_DATE) values ('");
                sb.append(l.get(0));
                sb.append("','");
                sb.append(l.get(1));
                sb.append("','");
                sb.append(l.get(2));
                sb.append("','");
                sb.append(l.get(3));
                sb.append("','");
                sb.append(l.get(4));
                sb.append("','");
                sb.append(l.get(5));
                sb.append("');");

                sb.append("\n");

            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        return sb;
    }

    private static Connection establishConnection()
    {
        System.out.println("-------- Oracle JDBC Connection  ------");

        try
        {

            Class.forName("oracle.jdbc.driver.OracleDriver");

        }
        catch (ClassNotFoundException e)
        {

            System.out.println("Where is your Oracle JDBC Driver?");
            e.printStackTrace();
            return null;

        }

        System.out.println("Oracle JDBC Driver Registered!");

        Connection connection = null;

        try
        {
            connection = DriverManager.getConnection(dbChainConnection, user, pass);
        }
        catch (SQLException e)
        {

            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return null;

        }

        if (connection != null)
        {
            System.out.println("You made it, take control your database now!");
        }
        else
        {
            System.out.println("Failed to make connection!");
        }

        return connection;
    }

}
